package com.scau.ums.util.dao;

import java.util.List;
import java.util.Map;

/**
 * @author xfy
 * @version 1.0
 * @date 2019/8/2 14:12
 */
public class UserSql {
    public String getStudentNumber(String searchKeyword){
        String sql = new String();
        sql = "select count(*) from student where concat(IFNULL(id,''),IFNULL(name,''),IFNULL(major,''),IFNULL(classes,'')," +
                "IFNULL(sex,''),IFNULL(telephone,''),IFNULL(mail,''),IFNULL(dormRegion,''),IFNULL(dormBuilding,''),IFNULL(dormRoom,'')) like '%" + searchKeyword + "%';";
        return sql;
    }
    public String getStudentListByLimit(int start, int number, String searchKeyword){
        String sql = new String();
        sql = "select * from student where concat(IFNULL(id,''),IFNULL(name,''),IFNULL(major,''),IFNULL(classes,'')," +
                "IFNULL(sex,''),IFNULL(telephone,''),IFNULL(mail,''),IFNULL(dormRegion,''),IFNULL(dormBuilding,''),IFNULL(dormRoom,'')) like '%" + searchKeyword + "%'" +
                " limit " + start + "," + number + ";";
        return sql;
    }

    public String getTeacherNumber(String searchKeyword){
        String sql = new String();
        sql = "select count(*) from teacher where concat(IFNULL(id,''),IFNULL(name,''),IFNULL(sex,''),IFNULL(telephone,'')," +
                "IFNULL(mail,''),IFNULL(dormRegion,''),IFNULL(dormBuilding,''),IFNULL(dormRoom,'')) like '%" + searchKeyword + "%';";
        return sql;
    }
    public String getTeacherListByLimit(int start, int number, String searchKeyword){
        String sql = new String();
        sql = "select * from teacher where concat(IFNULL(id,''),IFNULL(name,''),IFNULL(sex,''),IFNULL(telephone,''),IFNULL(mail,'')," +
                "IFNULL(dormRegion,''),IFNULL(dormBuilding,''),IFNULL(dormRoom,'')) like '%" + searchKeyword + "%' limit " + start + "," + number + ";";
        return sql;
    }

    public String getMaintainerNumber(String searchKeyword){
        String sql = new String();
        //三表连接查询
        sql = "select count(*) from maintainer a" +
                " LEFT JOIN maintainRole c on a.role = c.id LEFT JOIN maintainGroup b on a.maintainerGroup = b.id WHERE " +
                "concat(IFNULL(a.id,''),IFNULL(a.name,''),IFNULL(a.sex,''),IFNULL(a.telephone,''),IFNULL(a.mail,''),IFNULL(b.name,'')," +
                "IFNULL(c.name,'')) like '%" + searchKeyword + "%' ";

//        sql = "select count(*) from maintainer, maintainRole, maintainGroup WHERE (maintainer.maintainerGroup=maintainGroup.id and" +
//                " role=maintainRole.id) and concat(IFNULL(maintainer.id,''),IFNULL(maintainer.name,''),IFNULL(sex,''),IFNULL(telephone,'')," +
//                "IFNULL(mail,''),IFNULL(maintainGroup.name,''),IFNULL(maintainRole.name,'')) like '%" + searchKeyword + "%';";
        return sql;
    }

    public String getMaintainerListByLimit(int start, int number, String searchKeyword){
        String sql = new String();
        //三表连接查询
        sql = "select a.id ,a.name ,a.password,a.sex,a.telephone,a.mail,b.name as groups ,c.name as role from maintainer a" +
                " LEFT JOIN maintainRole c on a.role = c.id LEFT JOIN maintainGroup b on a.maintainerGroup = b.id WHERE " +
                "concat(IFNULL(a.id,''),IFNULL(a.name,''),IFNULL(a.sex,''),IFNULL(a.telephone,''),IFNULL(a.mail,''),IFNULL(b.name,'')," +
                "IFNULL(c.name,'')) like '%" + searchKeyword + "%' " +
                " limit " + start + "," + number + ";";

//        sql = " select maintainer.id as id,maintainer.name as name,password,sex,telephone,mail,maintainGroup.name " +
//            "as groups,maintainRole.name as role from maintainer, maintainRole, maintainGroup WHERE " +
//            "(maintainer.maintainerGroup=maintainGroup.id  and role=maintainRole.id) and concat(IFNULL(maintainer.id,''),IFNULL(maintainer.name,'')" +
//            ",IFNULL(sex,''),IFNULL(telephone,''),IFNULL(mail,''),IFNULL(maintainGroup.name,''),IFNULL(maintainRole.name,'')) like '%" + searchKeyword + "%' " +
//            " limit " + start + "," + number + ";";
        return sql;
    }

}
